package com.tgb.lk.table;

import java.sql.*;

import com.sun.rowset.CachedRowSetImpl;
import com.tgb.lk.util.AppConstants;

import javax.sql.rowset.CachedRowSet;

public class DBManager {

	private DBManager() {
	}

	public static DBManager getInstance() {
		return _instance;
	}

	public Connection getConnection(String url, String name, String passwd) {
		Connection conn = null;
		try {
			int data_type = TableConfig.getInstance().getDataBaseType();
			if (data_type == AppConstants.DB_SQLSERVER) {
                Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
            } else if (data_type == AppConstants.DB_ORACLE) {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} else {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			}
			conn = DriverManager.getConnection(url, name, passwd);
			conn.setAutoCommit(false);
		} catch (Exception e) {
			Log.log(e.getMessage(), 3);
		}
		return conn;
	}

	public Connection getConnection(int data_type, String url, String name,
			String passwd) {
		Connection conn = null;
		try {
			if (data_type == AppConstants.DB_SQLSERVER) {
                Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
            } else if (data_type == AppConstants.DB_ORACLE) {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} else {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			}
			conn = DriverManager.getConnection(url, name, passwd);
			conn.setAutoCommit(false);
		} catch (Exception e) {
			Log.log(e.getMessage(), 3);
		}
		return conn;
	}

	public void cleanup(Connection conn, Statement ps, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (Exception e) {
			Log.log(e);
		}
		try {
			if (ps != null) {
				ps.close();
				ps = null;
			}
		} catch (Exception e) {
			Log.log(e);
		}
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			Log.log(e);
		}
	}

	public int executeUpdate(String sql, Object values[]) {
		PreparedStatement st = null;
		Connection conn = null;
		try {
			conn = getConnection(
					TableConfig.getInstance().getDbConnectString(), TableConfig
							.getInstance().getDbUsername(), TableConfig
							.getInstance().getDbPasswd());
			conn.setAutoCommit(true);
			st = conn.prepareStatement(sql);
			for (int i = 0; values != null && i < values.length; i++) {
                st.setObject(i + 1, values[i]);
            }
			return st.executeUpdate();
		} catch (Exception se) {
			Log.log("SQLException in DBManager.exceuteUpdate, sql is :\r\n"
					+ sql, 2);
			Log.log(se);
		} finally {
			cleanup(conn, st, null);
		}
		return 0;
	}

	public CachedRowSet executeQuery(String sql, Object values[]) {
		CachedRowSet crs = null;
		PreparedStatement st = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			crs = new CachedRowSetImpl();
			conn = getConnection(
					TableConfig.getInstance().getDbConnectString(), TableConfig
							.getInstance().getDbUsername(), TableConfig
							.getInstance().getDbPasswd());
			st = conn.prepareStatement(sql);
			for (int i = 0; values != null && i < values.length; i++) {
                st.setObject(i + 1, values[i]);
            }

			rs = st.executeQuery();
			crs.populate(rs);
		} catch (Exception se) {
			Log.log("SQLException in DBManager.exceuteQuery, sql is :\r\n"
					+ sql, 2);
			Log.log(se);
		} finally {
			cleanup(conn, st, rs);
		}
		return crs;
	}

	private static DBManager _instance = new DBManager();

}